import sqlite3
import pandas as pd


# database initialisation
def database_init():
    # create database library and table book_info
    conn = sqlite3.connect("./library.db")
    cursor1 = conn.cursor()
    sql1 = """
    create table book_info(
    id INTEGER primary key,
    genre varchar(50),
    title varchar(20),
    author varchar(20),
    loan_periods int not null,
    purchase_date timestamp,
    member varchar(4) default "0"
    )
    """
    cursor1.execute(sql1)

    # create table loan_history
    cursor2 = conn.cursor()
    sql2 = """
    create table loan_history(
    transaction_id INTEGER primary key,
    book_id int,
    checkout_date timestamp,
    return_date timestamp
    )
    """
    cursor2.execute(sql2)

    # import book_info data from txt file
    cursor3 = conn.cursor()
    with open("book_info.txt") as f:
        # read all lines of the file
        file_data = f.readlines()
        for item in file_data:
            info = item.strip().split(",")
            sql3 = """
            insert into book_info (id, genre, title, author, loan_periods, purchase_date, member)values (?, ?, ?, ?, ?, ?, ?)
            """
            cursor3.execute(sql3, (info[0], info[1], info[2], info[3], info[4], info[5], info[6],))
            conn.commit()
    # import loan_history data from txt file
    cursor4 = conn.cursor()
    with open("loan_history.txt") as f:
        # read all lines of the file
        file_data = f.readlines()
        for item in file_data:
            info = item.strip().split(",")
            sql3 = """
            insert into loan_history values (?,?,?,?)
            """
            cursor4.execute(sql3, (info[0], info[1], info[2], info[3],))
            conn.commit()
    cursor1.close()
    cursor2.close()
    cursor3.close()
    cursor4.close()
    conn.close()


# select all books
def book_info():
    conn = sqlite3.connect("./library.db")
    sql = """
    select * from book_info
    """
    cursor = conn.cursor()
    cursor.execute(sql)
    return cursor.fetchall()
    # print(cursor.fetchall())
    cursor.close()
    conn.close()


# book search by id, genre, title, author, member
def book_search(id="", genre="", title="", author="", member=""):
    conn = sqlite3.connect("./library.db")
    sql = """
    select * from book_info where id=? or genre=? or title=? or author=? or member=?
    """
    cursor = conn.cursor()
    cursor.execute(sql, [id, genre, title, author, member])
    return cursor.fetchall()
    # print(cursor.fetchall())
    cursor.close()
    conn.close()


# check the book status by id
def book_check_out(book_id):
    conn = sqlite3.connect("./library.db")
    sql = """
    select member from book_info where id=?
    """
    cursor = conn.cursor()
    cursor.execute(sql, [book_id])
    for item in cursor.fetchone():
        return item
        # print(item)
    cursor.close()
    conn.close()


# sort the book id by loaned history desc
def book_recommend():
    conn = sqlite3.connect("./library.db")
    sql = """
    select book_id from loan_history group by book_id order by count(book_id) desc
    """
    cursor = conn.cursor()
    cursor.execute(sql)
    # print(cursor.fetchall())
    return cursor.fetchall()
    cursor.close()
    conn.close()


# select all overdue books
def book_overdue():
    conn = sqlite3.connect("./library.db")
    sql = """
    select b.id, b.loan_periods, b.member, h.checkout_date, h.return_date from book_info b, loan_history h
    where b.id = h.book_id order by h.transaction_id desc
    """
    cursor = conn.cursor()
    cursor.execute(sql)
    # print(cursor.fetchall())
    return cursor.fetchall()
    cursor.close()
    conn.close()


# find the loan history by book_id
def book_charge(member_id):
    conn = sqlite3.connect("./library.db")
    sql = """
    select h.checkout_date, b.loan_periods from loan_history h, book_info b
    where transaction_id in (select transaction_id from loan_history
    where book_id in (select id from book_info where member=?)
    order by transaction_id desc) order by transaction_id desc
    """
    cursor = conn.cursor()
    cursor.execute(sql, [member_id])
    # print(cursor.fetchone())
    return cursor.fetchone()
    cursor.close()
    conn.close()


# select loan_periods and member by book_id
def book_status(book_id):
    conn = sqlite3.connect("./library.db")
    sql = """
    select loan_periods, member from book_info where id=?
    """
    cursor = conn.cursor()
    cursor.execute(sql, [book_id])
    # print(cursor.fetchone())
    return cursor.fetchone()
    cursor.close()
    conn.close()


# get all book id
def get_book_id():
    conn = sqlite3.connect("./library.db")
    sql = """
    select id from book_info
    """
    cursor = conn.cursor()
    cursor.execute(sql)
    return cursor.fetchall()
    # print(cursor.fetchall())
    cursor.close()
    conn.close()


# update the book status when check out the book
def book_update(book_id, member_id):
    conn = sqlite3.connect("./library.db")
    sql = """
    update book_info set member=? where id=?
    """
    cursor = conn.cursor()
    cursor.execute(sql, [member_id, book_id])
    conn.commit()
    cursor.close()
    conn.close()


# check out book need to insert a new history
def insert_loan_history(book_id, checkout_date):
    conn = sqlite3.connect("./library.db")
    sql = """
    insert into loan_history(book_id, checkout_date) values(?, ?)
    """
    cursor = conn.cursor()
    cursor.execute(sql, [book_id, checkout_date])
    conn.commit()
    cursor.close()
    conn.close()


# update the book status when return the book
def book_return(book_id):
    conn = sqlite3.connect("./library.db")
    sql = """
    update book_info set member="0" where id=?
    """
    cursor = conn.cursor()
    cursor.execute(sql, [book_id])
    conn.commit()
    cursor.close()
    conn.close()


# return book need to update the history
def update_loan_history(transaction_id, return_date):
    conn = sqlite3.connect("./library.db")
    sql = """
    update loan_history set return_date=? where transaction_id=?
    """
    cursor = conn.cursor()
    cursor.execute(sql, [return_date, transaction_id])
    conn.commit()
    cursor.close()
    conn.close()


# get transaction id of the last check out history of this book
def get_transaction_id(book_id):
    conn = sqlite3.connect("./library.db")
    sql = """
    select transaction_id from loan_history where book_id=?
    order by transaction_id desc
     """
    cursor = conn.cursor()
    cursor.execute(sql, [book_id])
    for item in cursor.fetchone():
        return item
        # print(item)
    cursor.close()
    conn.close()


# select the checkout_date by transaction id
def get_checkout_date(transaction_id):
    conn = sqlite3.connect("./library.db")
    sql = """
    select checkout_date from loan_history where transaction_id=?
        """
    cursor = conn.cursor()
    cursor.execute(sql, [transaction_id])
    for item in cursor.fetchone():
        return item
        # print(item)
    cursor.close()
    conn.close()


# use pandas to analyse data
def pd_data():
    file = "./loan_history.txt"
    df = pd.read_csv(
        file,
        sep=",",
        header=None,
        names=("transaction_id", "book_id", "checkout_date", "return_date")
    )
    book_id = df["book_id"].value_counts()
    return book_id


if __name__ == "__main__":
    # test function please test one function one time
    # run and database will be initialised
    database_init()
    # list all books information
    book_info()
    # search book by id or genre or title or author or member
    #                9, classics, The Three Kingdoms, Anna well, caas
    book_search(id="", genre="", title="", author="", member="")
    # check book status by book_id such as "9", "5", "10"
    book_check_out(book_id="9")
    # list top 5 popular of all books
    book_recommend()
    # list all overdue books
    book_overdue()
    # select loan history by book_id such as "9", "7", "1"
    book_charge(book_id="7")
    # select loan_periods and member by book_id such as "10", "3"
    book_status(book_id="10")
    # list book_ids
    get_book_id()
    # update the member when someone check out the book
    # data "10" "oopp"
    book_update(book_id="10", member_id="oopp")
    # insert one new loan history after checking out the book
    # book_id:"6"  checkout_date:"2021-10-22"
    insert_loan_history(book_id="5", checkout_date="2021-10-22")
    # update member when someone return the book
    book_return(book_id="6")
    # update loan history when someone return the book
    # transaction_id
    update_loan_history(transaction_id="88", return_date="2021-01-25")
    # get transaction id of the last check out history of this book
    # book_id from 1 to 10
    get_transaction_id(book_id="5")
    # select the checkout_date by transaction id
    # transaction_id from 1 to 103
    get_checkout_date(transaction_id="")
    # the result analysis data
    pd_data()
